package com.dhcc.DRGsHosp.application.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import javax.inject.Named;

import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.transaction.annotation.Transactional;

import com.dhcc.DRGsHosp.application.DrgsIndexCalApplication;
import com.dhcc.DRGsHosp.application.SystemConfigApplication;
import com.dhcc.DRGsHosp.application.YiBaoHomeApplication;
import com.dhcc.DRGsHosp.core.domain.IndexCal;
import com.dhcc.DRGsHosp.infra.util.Arith;
import com.dhcc.DRGsHosp.infra.util.DrgsIndex;
import com.dhcc.DRGsHosp.infra.util.StringUtils;

/**
 * 
 * @title :YiBaoHomeApplicationImpl.java
 * @description :医保首页
 * @author :mazhaoyan
 * @date :2015年12月16日
 * @version :1.0
 * @copyright :DHCC 2013-2014
 */
@Named
@Transactional
public class YiBaoHomeApplicationImpl implements YiBaoHomeApplication {

	@Autowired
	SystemConfigApplication systemConfigApplication;

	@Autowired
	DrgsIndexCalApplication drgsIndexCalApplication;

	  @Autowired  
	  private JdbcTemplateObj jdbcTemplateObj; 
	   
	private QueryChannelService queryChannel;

	private QueryChannelService getQueryChannelService() {
		if (queryChannel == null) {
			queryChannel = InstanceFactory.getInstance(QueryChannelService.class, "queryChannel");
		}
		return queryChannel;
	}

	/**
	 * 
	 * @Description：全院医保基金
	 * @Author LiuHongJun
	 * @see com.dhcc.DRGsHosp.application.YiBaoHomeApplication#getHCInfoHosp(java.lang.String)
	 */
	@Override
	public Map<String, Object> getHCInfoHosp(String hiTypeo,String insType,String hospCode) {
		Object[] args = new Object[] {};
		StringBuilder sql = new StringBuilder();
		Map<String, Object> result = new HashMap<>();
		String maxDate = getMaxDate();// 出院病例最大日期
		if (null != maxDate && !"".equals(maxDate)) {
			int year = Integer.parseInt(maxDate.split("-")[0]);// 出院病例最大日期--年份
			String month = maxDate.split("-")[1];// 出院病例最大日期--月份
			String day = maxDate.split("-")[2];// 出院病例最大日期--日
			List<Map<String, Object>> data = new ArrayList<>();
			// 用来查询数据
			List<Map<String, Object>> listMap = new ArrayList<>();
			Map<String, Object> benchmark = new HashMap<>();
			benchmark.put("ybName", "本院医保总基金(万元)");
			// 查询本年的总基金
			args = new Object[] { year, "HCTotalFund_" + hiTypeo };
			sql.setLength(0);
			sql.append(" SELECT sum(a.medins_fund_value) as MANAGE_BENCHMARK FROM drgs.mr_medins_fund a WHERE  a.medins_fund_year= ? and a.medins_fund_code= ? \n");
			//增加院区
			sql.append(" and instr('"+hospCode+"',a.hosp_code)>0 \n");
			listMap = jdbcTemplateObj.queryForList(sql.toString(), args);
			if (!listMap.isEmpty()) {
				 
				benchmark.put("thisYear", listMap.get(0).get("MANAGE_BENCHMARK")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("MANAGE_BENCHMARK").toString()),10000,2 ));
			} else {
				benchmark.put("thisYear", null);
			}

			// 查询上年的总基金
			args = new Object[] { year - 1, "HCTotalFund_" + hiTypeo };
			sql.setLength(0);
			sql.append(" SELECT sum(a.medins_fund_value) as MANAGE_BENCHMARK FROM drgs.mr_medins_fund a WHERE  a.medins_fund_year= ? and a.medins_fund_code= ? \n");
			//增加院区
			sql.append(" and instr('"+hospCode+"',a.hosp_code)>0 \n");
			listMap = jdbcTemplateObj.queryForList(sql.toString(), args);
			if (!listMap.isEmpty()) {
				benchmark.put("lastYear", listMap.get(0).get("MANAGE_BENCHMARK")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("MANAGE_BENCHMARK").toString()),10000,2 ));
			} else {
				benchmark.put("lastYear", null);
			}
			data.add(benchmark);

			// 查询累计拨款值
			Map<String, Object> appropriation = new HashMap<>();
			appropriation.put("ybName", "累计拨款值(万元)");
			args = new Object[] { year + "01", year + "" + month, hiTypeo, year, "HCTotalFund_" + hiTypeo };
			sql.setLength(0);
			sql.append("select sum(a.medins_fund_value*b.perc) appropriation from drgs.mr_medins_fund a left join (select t.hosp_code,nvl(sum(t.mimp_value),0) perc from drgs.T_MIMONTHPROPORTION t \n");
			sql.append("where t.mimp_month between ? and ? and t.fund_type=? group by t.hosp_code)b on a.hosp_code=b.hosp_code ");
			sql.append("WHERE  a.medins_fund_year= ? and a.medins_fund_code= ? and instr('"+hospCode+"',a.hosp_code)>0 ");
			listMap = jdbcTemplateObj.queryForList(sql.toString(), args);
			if (!listMap.isEmpty()) {
				appropriation.put("thisYear",listMap.get(0).get("APPROPRIATION")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("APPROPRIATION").toString()),10000,2 ));
			} else {
				appropriation.put("thisYear", null);
			}
			// 查询上年累计拨款值
			sql.setLength(0);
			args = new Object[] { (year - 1) + "01", (year - 1) + "" + month, hiTypeo, year - 1, "HCTotalFund_" + hiTypeo };
			sql.append("select sum(a.medins_fund_value*b.perc) appropriation from drgs.mr_medins_fund a left join (select t.hosp_code,nvl(sum(t.mimp_value),0) perc from drgs.T_MIMONTHPROPORTION t \n");
			sql.append("where t.mimp_month between ? and ? and t.fund_type=? group by t.hosp_code)b on a.hosp_code=b.hosp_code ");
			sql.append("WHERE  a.medins_fund_year= ? and a.medins_fund_code= ? and instr('"+hospCode+"',a.hosp_code)>0 ");
			listMap = jdbcTemplateObj.queryForList(sql.toString(), args);
			if (!listMap.isEmpty()) {
				appropriation.put("lastYear",listMap.get(0).get("APPROPRIATION")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("APPROPRIATION").toString()),10000,2 ));
			} else {
				appropriation.put("lastYear", null);
			}
			data.add(appropriation);

			// 本年实际值
			Map<String, Object> actual = new HashMap<>();
			String dateFrom=DrgsIndex.formateDateFrom(year + "-01-01");
			String dateTo=DrgsIndex.formateDateTo(year + "-" + month + "-" + day);
			actual.put("ybName", "累计实际值(万元)");
			sql.setLength(0);
			sql.append("select sum(m.fund_pay) fundpay from drgs.t_medins m LEFT JOIN drgs.mr_ipinfo ipinfo ON m.parid=ipinfo.pmainindex left join drgs.mr_baseinfo base on m.parid=base.pmainindex \n");
			sql.append("where to_char(ipinfo.p00290000,'yyyy-mm-dd') between '"+dateFrom+"'  and   '"+dateTo+"'  AND  m.hi_type  in "+insType+" and instr('"+hospCode+"',base.p00010001)>0 \n");
			listMap = jdbcTemplateObj.queryForList(sql.toString());
			if (!listMap.isEmpty()) {
				actual.put("thisYear", listMap.get(0).get("FUNDPAY")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("FUNDPAY").toString()),10000,2 ));
			} else {
				actual.put("thisYear", null);
			}

			// 上年实际值
			 dateFrom=DrgsIndex.formateDateFrom((year-1) + "-01-01");
			 dateTo=DrgsIndex.formateDateTo((year-1) + "-" + month + "-" + day);
			 actual.put("ybName", "累计实际值(万元)");
			 sql.setLength(0);
			 sql.append("select sum(m.fund_pay) fundpay from drgs.t_medins m LEFT JOIN drgs.mr_ipinfo ipinfo ON m.parid=ipinfo.pmainindex left join drgs.mr_baseinfo base on m.parid=base.pmainindex \n");
			 sql.append("where to_char(ipinfo.p00290000,'yyyy-mm-dd') between '"+dateFrom+"'  and   '"+dateTo+"'  AND  m.hi_type  in "+insType+" and instr('"+hospCode+"',base.p00010001)>0 \n");
			 listMap = jdbcTemplateObj.queryForList(sql.toString());
			if (!listMap.isEmpty()) {
				actual.put("lastYear", listMap.get(0).get("FUNDPAY")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("FUNDPAY").toString()),10000,2 ));
			} else {
				actual.put("lastYear", null);
			}
			data.add(actual);

			// 查询本月拨款值
			Map<String, Object> appropriationMonth = new HashMap<>();
			appropriationMonth.put("ybName", "当月(" + month + ")拨款值(万元)");
			sql.setLength(0);
			String argsA=year + month,argsB=hiTypeo,argsD= "HCTotalFund_" + hiTypeo;
			int argsC=year;
			sql.append("select sum(a.medins_fund_value*b.perc) appropriation from drgs.mr_medins_fund a left join (select hosp_code,nvl(sum(t.mimp_value),0) perc from drgs.T_MIMONTHPROPORTION t \n");
			sql.append("where t.mimp_month ='"+argsA+"' and t.fund_type='"+argsB+"' and instr('"+hospCode+"',hosp_code)>0 group by t.hosp_code)b on a.hosp_code=b.hosp_code WHERE  a.medins_fund_year= '"+argsC+"' and a.medins_fund_code= '"+argsD+"' ");
			listMap = jdbcTemplateObj.queryForList(sql.toString());
			if (!listMap.isEmpty()) {
				appropriationMonth.put("thisYear", listMap.get(0).get("APPROPRIATION")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("APPROPRIATION").toString()),10000,2 ));
			} else {
				appropriationMonth.put("thisYear", null);
			}
			// 查询上年本月拨款值
			sql.setLength(0);
			argsA=(year-1) + month;
			argsB=hiTypeo;
			argsD= "HCTotalFund_" + hiTypeo;
		    argsC=year-1;
		    sql.append("select sum(a.medins_fund_value*b.perc) appropriation from drgs.mr_medins_fund a left join (select hosp_code,nvl(sum(t.mimp_value),0) perc from drgs.T_MIMONTHPROPORTION t \n");
			sql.append("where t.mimp_month ='"+argsA+"' and t.fund_type='"+argsB+"' and instr('"+hospCode+"',hosp_code)>0 group by t.hosp_code)b on a.hosp_code=b.hosp_code WHERE  a.medins_fund_year= '"+argsC+"' and a.medins_fund_code= '"+argsD+"' ");
			listMap = jdbcTemplateObj.queryForList(sql.toString());
			if (!listMap.isEmpty()) {
				appropriationMonth.put("lastYear",listMap.get(0).get("APPROPRIATION")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("APPROPRIATION").toString()),10000,2 ));
			} else {
				appropriationMonth.put("lastYear", null);
			}
			data.add(appropriationMonth);

			// 本月实际值
			Map<String, Object> actualMonth = new HashMap<String, Object>();
			actualMonth.put("ybName", "当月(" + month + ")实际值(万元)");
			argsA=DrgsIndex.formateDateFrom(year + "-" + month );
			argsB=DrgsIndex.formateDateTo(maxDate);
			sql.setLength(0);
			sql.append("select sum(m.fund_pay) fundpay from drgs.t_medins m LEFT JOIN drgs.mr_ipinfo ipinfo ON m.parid=ipinfo.pmainindex left join drgs.mr_baseinfo base on m.parid=base.pmainindex \n");
			sql.append("where to_char(ipinfo.p00290000,'yyyy-mm-dd') between '"+argsA+"'  and   '"+argsB+"'  AND  m.hi_type  in "+insType+" and instr('"+hospCode+"',base.p00010001)>0 \n");
			listMap = jdbcTemplateObj.queryForList(sql.toString());
			if (!listMap.isEmpty()) {
				actualMonth.put("thisYear", listMap.get(0).get("FUNDPAY")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("FUNDPAY").toString()),10000,2 ));
			} else {
				actualMonth.put("thisYear", null);
			}
			// 上年本月实际值
			args = new Object[] { DrgsIndex.formateDateFrom((year - 1) + "-" + month), DrgsIndex.formateDateTo((year - 1) + "-" + month), insType };
			argsA=DrgsIndex.formateDateFrom((year-1) + "-" + month );
			argsB= DrgsIndex.formateDateTo((year - 1) + "-" + month);
			sql.setLength(0);
			sql.append("select sum(m.fund_pay) fundpay from drgs.t_medins m LEFT JOIN drgs.mr_ipinfo ipinfo ON m.parid=ipinfo.pmainindex left join drgs.mr_baseinfo base on m.parid=base.pmainindex \n");
			sql.append("where to_char(ipinfo.p00290000,'yyyy-mm-dd') between '"+argsA+"'  and   '"+argsB+"'  AND  m.hi_type  in "+insType+" and instr('"+hospCode+"',base.p00010001)>0 \n");
			listMap = jdbcTemplateObj.queryForList(sql.toString());
			if (!listMap.isEmpty()) {
				actualMonth.put("lastYear", listMap.get(0).get("FUNDPAY")==null?null:Arith.div(Double.valueOf(listMap.get(0).get("FUNDPAY").toString()),10000,2 ));
			} else {
				actualMonth.put("lastYear", null);
			}
			data.add(actualMonth);

			result.put("data", data);
			result.put("year", year);
			result.put("month", month);
		}
		return result;
	}
/**
 * 
 *  @Description：获取医保基金数据
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.YiBaoHomeApplication#getHCHistory(java.lang.String)
 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getHCHistory' + #hiTypeo +#insType +#hospCode")
	public List<Object[]> getHCHistory(String hiTypeo,String insType,String hospCode) {

		// 查询最大的出院日期
		StringBuilder sql = new StringBuilder();
		int year = 0;// 年份
		if(null != getMaxDate() && !"".equals(getMaxDate())){
			 year = Integer.parseInt(getMaxDate().split("-")[0]);
		}
		// 查询本年的总基金
		sql.setLength(0);
		sql.append(" select round(mimp_month/10000,2),round(moncost/10000,2), round(montarcost/10000,2),round((moncost - montarcost)/10000,2) diff, \n ");
		sql.append(" case when substr(mimp_month,5,1)=0 then substr(mimp_month,6,1)||'月' else substr(mimp_month,5,2)||'月' end mimp_month from ( \n ");
		sql.append(" select b.mimp_month, nvl(a.moncost,0) moncost, b.montarcost from ( \n ");
		sql.append(" select sum(t.fund_pay) moncost,to_char(ip.p00290000,'yyyymm') mon from drgs.t_medins t  \n ");
		sql.append(" left join drgs.mr_ipinfo ip on ip.pmainindex = t.parid left join drgs.mr_baseinfo base on t.parid=base.pmainindex where  \n ");
		sql.append(" to_char(ip.p00290000,'yyyy') = '" + year + "' AND  t.hi_type  in "+insType+" and instr('"+hospCode+"',base.p00010001)>0 \n ");
		sql.append(" group by to_char(ip.p00290000,'yyyymm') \n ");
		sql.append(" ) a \n ");
		sql.append(" left join ( \n ");
		sql.append(" select mp.mimp_month,sum(total*mimp_value) montarcost from drgs.t_mimonthproportion mp \n ");
		sql.append(" left join (select qm.hosp_code, nvl(qm.medins_fund_value,0) total from drgs.mr_medins_fund qm\n ");
		sql.append(" where qm.medins_fund_year = '" + year + "' and qm.medins_fund_code = 'HCTotalFund_" + hiTypeo + "') b on mp.hosp_code=b.hosp_code \n ");
		sql.append(" where substr(mimp_month, 0, 4) = '" + year + "' group by mp.mimp_month \n ");
		sql.append(" ) b \n ");
		sql.append(" on a.mon= b.mimp_month) order by mimp_month  \n ");
		List<Object[]> monthCostList = getQueryChannelService().createSqlQuery(sql.toString()).list();
		return monthCostList;
	}
/**
 * 
 *  @Description：科室监测
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.YiBaoHomeApplication#depQuotaMonitor()
 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'depQuotaMonitor'+ #maxDate +#asId +#hospCode +#groupScheme")
	public Map<String, Object> depQuotaMonitor(String maxDate,String asId,String hospCode,String groupScheme) {
		if (null != maxDate && !"".equals(maxDate)) {
			maxDate = maxDate.substring(0, 7);
		}
		Map<String, Object> result = new HashMap<String, Object>();
		// ------次均费用----平均住院日-----药占比--------//
		// 年初管理目标
		List<Object[]> targetL = compareWithYearStartTarget(maxDate,hospCode);// 年初管理值 = 科室绩效
		// 标杆管理目标
		List<Object[]> benchmark = compareWithBenchmark(maxDate,asId,hospCode,groupScheme);// 标杆管理值 = 当期病组结构*病组标杆[最新]
		// 实际管理目标
		List<Object[]> targetT = compareWithTarget(maxDate,hospCode,groupScheme);// 实际管理值 =当前病组结构*病组目标
		result.put("targetL", targetL);
		result.put("benchmark", benchmark);
		result.put("targetT", targetT);

		return result;
	}
/**
 * 
 *  compareWithYearStartTarget.  
 *  @Description：年初管理目标
 *  @Author LiuHongjun
 *  @param maxDate
 *  @return 
 *  @Date:2017年6月22日上午7:42:43
 */
	private List<Object[]> compareWithYearStartTarget(String maxDate,String hospCode) {
		List<Object[]> comapreList = new LinkedList<Object[]>();
		if (maxDate != null && !"".equals(maxDate)) {
			String year = maxDate.substring(0, 4);// 出院病例最大日期年份
			String mYear = drgsIndexCalApplication.getRecentlyManageYear(year, "manager_inner", "dep");// 管理目标年份
			String sql = "select a.out_dep_code,a.feeval,a.dayval,a.drugval,t_fee.feetar,t_day.daytar,t_drug.drugtar,dep.dep_name from   ( \n"
					+ "  select  ip.P00300001 out_dep_code,avg(ip.P00800000) feeval,avg(ip.P00320000) dayval,case when sum(ip.P00800000)<>0 and sum(exsum.drug_cost) <>0 then sum(exsum.drug_cost)/sum(ip.P00800000)*100 else 0 end drugval \n "
					+ " from drgs.mr_ipinfo ip left join drgs.mr_expense_sum exsum on ip.pmainindex=exsum.pmainindex \n"
					+"  left join  drgs.mr_baseinfo bs on bs.pmainindex=ip.pmainindex and instr('"+hospCode+"',bs.p00010001)>0  \n"
					+ " where to_char(ip.P00290000,'yyyy-mm')= '" + maxDate + "'  group by ip.P00300001 \n"
					+ " ) a left join ( \n"
					+ " select qm.pardep_code,qm.manage_benchmark feetar from drgs.t_quotamanage qm where qm.manage_year = '"+ mYear + "' and qm.pardep_code is not null \n "
					+ " and qm.pardrg_code is null and qm.pardepgroup_code is null  AND qm.patient_type='0' and qm.parquota_code = 'AvgFee' )t_fee  on a.out_dep_code = t_fee.pardep_code \n "
					+ " left join( \n"
					+ " select qm.pardep_code,qm.manage_benchmark daytar from drgs.t_quotamanage qm where qm.manage_year = '"+ mYear + "' \n"
					+ " and qm.pardrg_code is null and qm.pardepgroup_code is null  AND qm.patient_type='0' and qm.parquota_code = 'AvgDays')t_day on a.out_dep_code = t_day.pardep_code \n "
					+ " left join  ( \n"
					+ " select qm.pardep_code,qm.manage_benchmark drugtar from drgs.t_quotamanage qm where qm.manage_year = '"+ mYear + "' \n"
					+ " and qm.pardrg_code is null and qm.pardepgroup_code is null AND qm.patient_type='0'   and qm.parquota_code = 'DrugRatio')t_drug \n"
					+ " on a.out_dep_code = t_drug.pardep_code left join drgs.t_department dep on a.out_dep_code = dep.dep_code order by a.out_dep_code \n ";
			comapreList = getQueryChannelService().createSqlQuery(sql).list();
		}

		return comapreList;

	}

	/**
	 * 
	 *  compareWithTarget.  
	 *  @Description：实际管理值 
	 *  @Author LiuHongjun
	 *  @param maxDate
	 *  @return 
	 *  @Date:2017年6月22日下午7:40:19
	 */
	private List<Object[]> compareWithTarget(String maxDate,String hospCode,String groupScheme) {
		List<Object[]> comapreList = new LinkedList<Object[]>();
		if (maxDate != null && !"".equals(maxDate)) {
			String year = maxDate.substring(0, 4);// 出院病例最大日期年份
			String mYear = drgsIndexCalApplication.getRecentlyManageYear(year, "manager_inner", "dep");// 管理目标年份
			String sql = "select a.out_dep_code,a.feeval,a.dayval,a.drugval,b.feetar,b.daytar,b.drugtar,dep.dep_name from ( \n"
					+ " select  ip.p00300001 out_dep_code,avg(ip.P00800000) feeval,avg(ip.P00320000) dayval,case when sum(ip.P00800000)<>0 then sum(exsum.drug_cost)/sum(ip.P00800000)*100 else 0 end drugval from drgs.mr_ipinfo ip \n"
					+ " left join drgs.mr_expense_sum exsum on ip.pmainindex=exsum.pmainindex  \n"
					+ " left join  drgs.mr_baseinfo bs on bs.pmainindex=ip.pmainindex  \n"
					+ " where to_char(ip.P00290000,'yyyy-mm')= '"+ maxDate + "' and instr('"+hospCode+"',bs.p00010001)>0 group by ip.p00300001)a \n"
					+ " left join ( \n"
					+ " select out_dep_code,case when sum(case when fee.manage_benchmark>0 then cnt else 0 end)<>0 then sum(case when fee.manage_benchmark>0 then cnt else 0 end * fee.manage_benchmark)/sum(case when fee.manage_benchmark>0 then cnt else 0 end) else 0 end feetar, \n"
					+ " case when sum(case when day.manage_benchmark>0 then cnt else 0 end)<>0 then sum(case when day.manage_benchmark>0 then cnt else 0 end * day.manage_benchmark)/sum(case when day.manage_benchmark>0 then cnt else 0 end) else 0 end daytar,\n"
					+ " case when sum(case when drug.manage_benchmark>0 then cnt else 0 end)<>0 then sum(case when drug.manage_benchmark>0 then cnt else 0 end * drug.manage_benchmark)/sum(case when drug.manage_benchmark>0 then cnt else 0 end) else 0 end drugtar \n"
					+ " from ( \n"
					+ " select count(*) cnt, ip.p00300001 out_dep_code ,pd.pardrg_code   from drgs.t_patientdrg pd left join drgs.mr_ipinfo ip on ip.pmainindex = pd.parid and pd.ver_region='"+groupScheme+"'  \n"
					+ " left join  drgs.mr_baseinfo bs on bs.pmainindex=ip.pmainindex \n"
					+ " where to_char(ip.P00290000,'yyyy') = '" + year+ "' and pd.pardrg_code is not null and instr('"+hospCode+"',bs.p00010001)>0 group by  ip.p00300001 ,pd.pardrg_code order by   ip.p00300001 ) a \n "
					+ " left join ( \n"
					+ " select t.pardep_code,t.pardrg_code,t.manage_benchmark from drgs.t_quotamanage t where t.manage_year = '"+ mYear+ "' and t.pardep_code is not null and t.pardrg_code is not null and t.pardepgroup_code is null \n "
					+ "  AND t.patient_type='0'  and t.parquota_code = 'AvgFee' )fee on a.out_dep_code = fee.pardep_code and a.pardrg_code = fee.pardrg_code \n"
					+ " left join ( \n"
					+ " select t.pardep_code,t.pardrg_code,t.manage_benchmark from drgs.t_quotamanage t where t.manage_year = '"+ mYear
					+ "' and t.pardep_code is not null and t.pardrg_code is not null and t.pardepgroup_code is null \n"
					+ "  AND t.patient_type='0'  and t.parquota_code = 'AvgDays') day on a.out_dep_code = day.pardep_code and a.pardrg_code = day.pardrg_code \n"
					+ " left join( \n" 
					+ " select t.pardep_code,t.pardrg_code,t.manage_benchmark from drgs.t_quotamanage t where t.manage_year = '"+ mYear+ "' \n"
					+ " and t.pardep_code is not null and t.pardrg_code is not null and t.pardepgroup_code is null \n "
					+ "  AND t.patient_type='0' and t.parquota_code = 'DrugRatio') drug on a.out_dep_code = drug.pardep_code and a.pardrg_code = drug.pardrg_code \n"
					+ " group by out_dep_code) b   on a.out_dep_code = b.out_dep_code \n"
					+ " left join drgs.t_department dep  on a.out_dep_code = dep.dep_code  order by a.out_dep_code  \n";
			comapreList = getQueryChannelService().createSqlQuery(sql).list();
		}
		return comapreList;
	}

	/**
	 * 
	 *  compareWithBenchmark.  
	 *  @Description：标杆管理目标
	 *  @Author LiuHongjun
	 *  @param maxDate
	 *  @return 
	 *  @Date:2017年6月22日下午7:39:32
	 */
	private List<Object[]> compareWithBenchmark(String maxDate,String asId,String hospCode,String groupScheme) {
		List<Object[]> comapreList = new LinkedList<Object[]>();
		if (null != maxDate && !"".equals(maxDate)) {
		  String year = maxDate.substring(0, 4);// 出院病例最大日期年份
		  String sql="  select a.out_dep_code,a.feeval,a.dayval,a.drugval,b.feetar,b.daytar,b.drugtar,dep.dep_name from ( \n"
					+ "	 select dep.dep_code out_dep_code ,avg(ip.P00800000) feeval,avg(ip.P00320000) dayval,case when sum(ip.P00800000)<>0 and sum(exsum.drug_cost)<>0 then sum(exsum.drug_cost)/sum(ip.P00800000)*100 else 0 end drugval \n "
					+ "  from drgs.mr_ipinfo ip   left join drgs.mr_expense_sum exsum on ip.pmainindex=exsum.pmainindex \n"
					+ "  left join  drgs.mr_baseinfo bs on bs.pmainindex=ip.pmainindex  "
					+ "  left join drgs.t_department dep on ip.P00300001=dep.dep_code \n"
					+ "  where to_char(ip.p00290000,'yyyy-mm')=  '" + maxDate + "' and instr('"+hospCode+"',bs.p00010001)>0 group by dep.dep_code)a "
					+ "  left join ( \n"
					+ "  select out_dep_code, "
					+ "  case when sum(case when as_avg_fee>0 then cnt else 0 end)<>0 then sum(case when as_avg_fee>0 then cnt else 0 end * as_avg_fee)/sum(case when as_avg_fee>0 then cnt else 0 end) else 0 end feetar, \n"
					+ "  case when sum(case when as_avg_days>0 then cnt else 0 end)<>0 then sum(case when as_avg_days>0 then cnt else 0 end * as_avg_days)/sum(case when as_avg_days>0 then cnt else 0 end) else 0 end daytar, \n"
					+ "  case when sum(case when as_drug_ratio>0 then cnt else 0 end)<>0 then sum(case when as_drug_ratio>0 then cnt else 0 end * as_drug_ratio)/sum(case when as_drug_ratio>0 then cnt else 0 end) else 0 end drugtar  \n"
					+ "  from ( "
					+ "  select count(*) cnt,dep.dep_code  out_dep_code ,pd.pardrg_code  \n"
					+ "  from drgs.t_patientdrg pd left join drgs.mr_ipinfo ip   on ip.pmainindex = pd.parid and pd.ver_region='"+groupScheme+"' \n"
					+ "  left join  drgs.mr_baseinfo bs on bs.pmainindex=ip.pmainindex \n"
					+ "  left join drgs.t_department dep on ip.P00300001=dep.dep_code where to_char(ip.P00290000,'yyyy') = '" + year + "'  and pd.pardrg_code is not null and instr('"+hospCode+"',bs.p00010001)>0 \n "
					+ "  group by  dep.dep_code ,pd.pardrg_code  order by   dep.dep_code  ) a \n"
					+ "  left join ( "
					+ "  select as_drg_code,as_avg_fee,as_avg_days,0 as_drug_ratio from drgs.t_annualstd annual where annual.as_id = '"+asId+"'  \n"
					+ " ) b on a.pardrg_code = b.as_drg_code  group by out_dep_code ) b \n"
					+ " on a.out_dep_code = b.out_dep_code left join drgs.t_department dep  on a.out_dep_code = dep.dep_code  order by a.out_dep_code \n";
			comapreList = getQueryChannelService().createSqlQuery(sql).list();
		}
		return comapreList;
	}

	/**
	 * 
	 * @methodname :compareResult
	 * @description :
	 * @param :
	 * @author :mazhaoyan
	 * @return :
	 * @date :2016年1月6日
	 */
	private Map<String, Object> compareResult(List<Object[]> dataList) {
		Map<String, Object> result = new HashMap<String, Object>();
		int l = dataList.size();
		List<Object[]> all = new ArrayList<Object[]>();
		List<Object[]> dayfee = new ArrayList<Object[]>();
		List<Object[]> daydrug = new ArrayList<Object[]>();
		List<Object[]> feedrug = new ArrayList<Object[]>();
		List<Object[]> day = new ArrayList<Object[]>();
		List<Object[]> fee = new ArrayList<Object[]>();
		List<Object[]> drug = new ArrayList<Object[]>();
		for (int i = 0; i < l; i++) {
			boolean dayb = false, feeb = false, drugb = false;
			if (dataList.get(i)[1] != null && dataList.get(i)[4] != null && Double
					.parseDouble(dataList.get(i)[1].toString()) > Double.parseDouble(dataList.get(i)[4].toString())) {// 当前科室次均费用超标
				feeb = true;
			}
			if (dataList.get(i)[2] != null && dataList.get(i)[5] != null && Double
					.parseDouble(dataList.get(i)[2].toString()) > Double.parseDouble(dataList.get(i)[5].toString())) {// 当前科室平均住院日超标
				dayb = true;
			}
			if (dataList.get(i)[3] != null && dataList.get(i)[6] != null && Double
					.parseDouble(dataList.get(i)[3].toString()) > Double.parseDouble(dataList.get(i)[6].toString())) {// 当前科室药占比超标
				drugb = true;
			}
			if (dayb && feeb && drugb) {// 平均住院日|次均费用|药占比三个指标都超标
				all.add(dataList.get(i));
				dayfee.add(dataList.get(i));
				feedrug.add(dataList.get(i));
				daydrug.add(dataList.get(i));
				day.add(dataList.get(i));
				fee.add(dataList.get(i));
				drug.add(dataList.get(i));
			} else if (dayb && feeb) {// 平均住院日|次均费用两个指标未超标
				dayfee.add(dataList.get(i));
				day.add(dataList.get(i));
				fee.add(dataList.get(i));
			} else if (feeb && drugb) {// 次均费用|药占比两个指标未超标
				feedrug.add(dataList.get(i));
				fee.add(dataList.get(i));
				drug.add(dataList.get(i));
			} else if (dayb && drugb) {// 平均住院日|药占比两个指标超标
				daydrug.add(dataList.get(i));
				day.add(dataList.get(i));
				drug.add(dataList.get(i));
			} else if (dayb) {// 平均住院日指标超标
				day.add(dataList.get(i));
			} else if (feeb) {// 次均费用指标超标
				fee.add(dataList.get(i));
			} else if (drugb) {// 药占比指标超标
				drug.add(dataList.get(i));
			}
		}
		result.put("all", all);
		result.put("dayfee", dayfee);
		result.put("feedrug", feedrug);
		result.put("daydrug", daydrug);
		result.put("day", day);
		result.put("fee", fee);
		result.put("drug", drug);
		return result;
	}
/**
 * 
 *  @Description：获取医保中最新的出院时间
 *  @Author LiuHongJun  
 *  @see com.dhcc.DRGsHosp.application.YiBaoHomeApplication#getMaxDate()
 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getYIBAOMaxDate'")
	public String getMaxDate() {
		String maxDate=null;
		Map<String, Object> dates = new HashMap<>();
		String sql = "SELECT TO_CHAR(MAX(ipinfo.p00290000),'YYYY-MM-DD') MAXDATE FROM DRGS.T_MEDINS M  \n"
				+ " LEFT JOIN drgs.mr_ipinfo ipinfo ON m.parid=ipinfo.pmainindex \n";
		try {
			dates = jdbcTemplateObj.queryForMap(sql.toString());
		} catch (Exception e) {
			dates = new HashMap<>();
		}
		if (!dates.isEmpty()) {
			 maxDate = dates.get("MAXDATE") + "";// 出院病例最大日期}
		}
		return maxDate;
	}
}
